
/**
	AUTHOR		: SUJAN
	DATE		: MARCH 03rd 2005
	DESCRIPTION	: THIS FUNCTION IS USED TO GET THE DETAILS OF THE SHIPPED ORDERS.
*/
ALTER FUNCTION FN_FORECAST_LOAD_SHIPPED_ORDERS(
  
  @CUSTOMER_ID INT,
  @PRODUCT_ID INT,
  @MONTH_FROM INT, --STARTS FROM ZERO.
  @SHOW_NEXT INT,  --NUMBER OF MONTHS TO BE FETCHED STARTING FROM  @MONTH_FROM. STARTS FROM 1.
  @YEAR_START INT ) --STARTING YEAR
  
  RETURNS @FN_RESULTS TABLE( CUSTOMER VARCHAR(100), PRODUCT VARCHAR(100), MONTH INT, YEAR INT, QUANTITY INT, UNIT_PRICE FLOAT )
  
  AS
  BEGIN
  	DECLARE @OLD_PRICE FLOAT

	DECLARE @TEMP_CURSOR CURSOR 
  
	IF @MONTH_FROM < 0
	BEGIN
		SET @MONTH_FROM = 0
	END

	IF @SHOW_NEXT <= 0
	BEGIN
		SET @SHOW_NEXT = 11
	END
	
	DECLARE @TO_MONTH INT	
	SET @TO_MONTH = @MONTH_FROM + @SHOW_NEXT 

	DECLARE @YEAR_TO INT
	
	
	SET @YEAR_TO = @YEAR_START + @TO_MONTH/12 
	SET @SHOW_NEXT = @TO_MONTH%12  	

  
  	IF @CUSTOMER_ID IS NULL OR @CUSTOMER_ID <= 0
  	BEGIN
  		SET @CUSTOMER_ID = 0
  	END
  
  	IF @CUSTOMER_ID = 0 AND @PRODUCT_ID = 0
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
		WHERE ( ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_START AND ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @TO_MONTH ) OR
	  		      ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @SHOW_NEXT AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_START <> @YEAR_TO ) OR
	  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_START ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END	
  	ELSE IF @CUSTOMER_ID = 0 AND @PRODUCT_ID <> 0
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
		WHERE P.PRODUCT_ID = @PRODUCT_ID 
		   AND	( ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_START AND ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @TO_MONTH ) OR
	  		      ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @SHOW_NEXT AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_START <> @YEAR_TO ) OR
	  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_START ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END
  	ELSE IF @CUSTOMER_ID <> 0 AND @PRODUCT_ID = 0
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
		WHERE  CUST.CUSTOMER_ID = @CUSTOMER_ID 
	  	   	AND ( ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_START AND ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @TO_MONTH ) OR
	  		      ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @SHOW_NEXT AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_START <> @YEAR_TO ) OR
	  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_START ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END
  	ELSE IF @CUSTOMER_ID <> 0 AND @PRODUCT_ID <> 0
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
		WHERE  CUST.CUSTOMER_ID = @CUSTOMER_ID
	  		AND P.PRODUCT_ID = @PRODUCT_ID
			AND  ( ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_START AND ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @TO_MONTH ) OR
	  		      ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ))-1 < @SHOW_NEXT AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_START <> @YEAR_TO ) OR
	  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_START ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END
 
  	DECLARE @CUSTOMER VARCHAR(100)
  	DECLARE @PRODUCT VARCHAR(100)
  	DECLARE @MONTH INT
  	DECLARE @YEAR INT
  	DECLARE @QUANTITY INT
  	DECLARE @OLD_QUANTITY INT
  	DECLARE @UNIT_PRICE FLOAT
  	DECLARE @ROW_COUNT INT
  	
  	OPEN @TEMP_CURSOR 
  		FETCH NEXT FROM @TEMP_CURSOR
  		INTO @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
  		
  		WHILE @@FETCH_STATUS = 0
  		BEGIN
  			SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS 
  			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
  				
  			IF @ROW_COUNT = 0
  			BEGIN
  				INSERT INTO @FN_RESULTS VALUES ( @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @QUANTITY * @UNIT_PRICE)
  			END
  
  			ELSE
  			BEGIN
  				SELECT @OLD_QUANTITY = QUANTITY, @OLD_PRICE = UNIT_PRICE FROM @FN_RESULTS
  				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
  
  				UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY) , UNIT_PRICE = @OLD_PRICE +  ( @QUANTITY * @UNIT_PRICE )
  				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
  			END	
			  			
  			FETCH NEXT FROM @TEMP_CURSOR
  			INTO  @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
  		END
  
  	CLOSE @TEMP_CURSOR
  	DEALLOCATE @TEMP_CURSOR
   
  	RETURN
  END
 
